﻿using System;
using System.Collections.Generic;
using System.Data;
using Common;
using Entity;
using System.Data.SqlClient;

namespace DataAccess
{
    /// <summary>
    /// Summary description for ArticleDAL
    /// </summary>
    public class ArticleDAL : AbstractDataAccess
    {
        private readonly CategoryDAL _categoryDAL;
        private List<SqlParameter> _parameters;

        /// <summary>
        /// Initializes a new instance of the <see cref="ArticleDAL"/> class.
        /// </summary>
        public ArticleDAL()
        {
            _categoryDAL = new CategoryDAL();
        }

        /// <summary>
        /// Determines whether the specified id is existed.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns>
        ///   <c>true</c> if the specified id is existed; otherwise, <c>false</c>.
        /// </returns>
        public bool IsExisted(Guid id)
        {
            _parameters = new List<SqlParameter>();
            _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterId, Value = id });
            object result = ExecuteQueryAsScalar(Constant.CheckArticle, _parameters);
            if (result != null)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// Updates the specified article.
        /// </summary>
        /// <param name="article">The article.</param>
        /// <returns></returns>
        public bool Update(Article article)
        {
            var articleId = article.Id;
            if(!IsExisted(articleId))
            {
                throw new Exception("Id was not existed");
            }

            var title = article.Title;
            var content = article.Content;
            var categoryId = new Guid(); 
            if(article.Category != null)
            {
                categoryId = article.Category;
            }
            
            var brief = article.Brief;
            var isActive = article.IsActive;

            OpenConnection(ConnectionObject);
            SqlTransaction transaction = null;
            try
            {
                transaction = ConnectionObject.BeginTransaction();

                if (title != null)
                {
                    _parameters = new List<SqlParameter>();
                    _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterTitle, Value = title });
                    _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterId, Value = articleId });
                    ExecuteNonQuery(Constant.UpdateArticleTitle, _parameters, transaction);
                }
                if (content != null)
                {
                    _parameters = new List<SqlParameter>();
                    _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterContent, Value = content });
                    _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterId, Value = articleId });
                    ExecuteNonQuery(Constant.UpdateArticleContent, _parameters, transaction);
                }
                bool categoryIsExisted = _categoryDAL.IsExisted(categoryId);
                if (categoryIsExisted)
                {
                    _parameters = new List<SqlParameter>();
                    _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterCategory, Value = categoryId });
                    _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterId, Value = articleId });
                    ExecuteNonQuery(Constant.UpdateArticleCategory, _parameters, transaction);                                  
                }
                if (brief != null)
                {
                    _parameters = new List<SqlParameter>();
                    _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterBrief, Value = brief });
                    _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterId, Value = articleId });
                    ExecuteNonQuery(Constant.UpdateArticleBrief, _parameters, transaction);
                }
                return true;
            }
            catch (Exception)
            {
                try
                {
                    if (transaction != null) transaction.Rollback();
                }
                catch (InvalidOperationException)
                {

                }
            }
            finally
            {
                if (transaction != null) transaction.Commit();
                CloseConnection(ConnectionObject);
            }
            return false;
        }

        public DataTable GetTopNew(int top)
        {
            _parameters = new List<SqlParameter>();
            _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterTop, Value = top });
            return ExecuteQueryAsSqlDataTable(Constant.GetTopArticlesByPostedDate, _parameters);
        }

        public DataTable GetById(Guid id)
        {
            _parameters = new List<SqlParameter>();
            _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterId, Value = id });
            return ExecuteQueryAsSqlDataTable(Constant.GetArticleById, _parameters);
        }

        public PaginatedInfo<Article> GetAll(string keyWord, int pageIndex, int pageSize)
        {
            var from = ((pageIndex - 1) * pageSize) + 1;
            var to = from + pageSize - 1;
            //Count record
            _parameters = new List<SqlParameter>();
            _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterKeyWord, Value = String.Format("%{0}%", keyWord) });
            var totalRecords = (int)ExecuteQueryAsScalar(Constant.GetCountArticlesByKeyWord, _parameters);
            //Paging
            _parameters = new List<SqlParameter>();
            _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterKeyWord, Value = String.Format("%{0}%", keyWord) });
            _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterFrom, Value = from });
            _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterTo, Value = to });
            var dataTable = ExecuteQueryAsSqlDataTable(Constant.GetArticlesByKeyword, _parameters);
            //Return paging information
            return new PaginatedInfo<Article>(dataTable, pageIndex, totalRecords, pageSize);
        }

        public bool Block(Guid id)
        {
            _parameters = new List<SqlParameter>();
            _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterIsActive, Value = false });
            _parameters.Add(new SqlParameter { ParameterName = Constant.ParameterId, Value = id });
            return ExecuteNonQuery(Constant.UpdateArticleIsActive, _parameters);
        }
    }
}